﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Fodee.DataAccess
{
    public class DataBase : IDisposable
    {
        #region Constructor
        public DataBase()
        {

        }
        #endregion

        #region Create connection
        private SqlConnection con = null;
        #endregion

        #region   Open connection
        /// <summary>
        /// Open connection.
        /// </summary>
        private void Open()
        {
            if (con == null)
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            }
            if (con.State == System.Data.ConnectionState.Closed)
            {
                con.Open();
            }

        }
        #endregion

        #region  Close connection
        /// <summary>
        /// Close connection
        /// </summary>
        public void Close()
        {
            if (con != null && con.State != System.Data.ConnectionState.Closed)
            {
                con.Close();
            }
        }
        #endregion

        #region Dispose connection
        /// <summary>
        /// Dispose connection
        /// </summary>
        public void Dispose()
        {
            if (con != null)
            {
                con.Dispose();
                con = null;
            }
        }
        #endregion

        public enum CommandOperation { None, Insert, Update, Delete, Query }

        #region   Create SqlParameter
        /// <summary>
        /// Create SqlParameter
        /// </summary>
        /// <param name="ParamName">ParamName</param>
        /// <param name="DbType">DbType</param></param>
        /// <param name="Size">Size</param>
        /// <param name="Value">Value</param>
        /// <returns>SqlParameter</returns>
        public SqlParameter AddParameter(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return AddParameter(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        /// <summary>
        /// Init SqlParameter value
        /// </summary>
        /// <param name="ParamName">ParamName</param>
        /// <param name="DbType">DbType</param>
        /// <param name="Size">Size</param>
        /// <param name="Direction">Direction</param>
        /// <param name="Value">Value</param>
        /// <returns>SqlParameter</returns>
        public SqlParameter AddParameter(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;

            if (Size > 0)
                param = new SqlParameter(ParamName, DbType, Size);
            else
                param = new SqlParameter(ParamName, DbType);

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;
            return param;
        }


        public SqlParameter[] CreateParameters(DataSet ds, CommandOperation commandOperation, string tableName)
        {
            int lenParam = ds.Tables[tableName].Columns.Count;

            if (commandOperation == CommandOperation.Delete)
            {
                lenParam = ds.Tables[tableName].PrimaryKey.Length;
            }

            SqlParameter[] param = new SqlParameter[lenParam];
            for (int i = 0; i < lenParam; i++)
            {
                param[i] = new SqlParameter();
                param[i].ParameterName = string.Format("@{0}", ds.Tables[tableName].Columns[i].ColumnName);
                param[i].SourceColumn = ds.Tables[tableName].Columns[i].ColumnName;
            }

            return param;
        }
        #endregion

        #region   Excute nonquery command



        /// <summary>
        /// Excute nonquery command
        /// </summary>
        /// <param name="procName">procName</param>
        /// <param name="prams">prams</param>
        /// <returns></returns>
        public bool ExcuteNonQuery(string procName, DataSet ds, CommandOperation commandOperation, string tableName)
        {
            try
            {
                
                SqlParameter[] param = CreateParameters(ds, commandOperation, tableName);
                SqlCommand cmd = CreateCommand(procName, param);
                SqlDataAdapter adapter = new SqlDataAdapter();

                switch (commandOperation)
                {
                    case CommandOperation.Insert:
                        adapter.InsertCommand = cmd;
                        break;
                    case CommandOperation.Update:
                        adapter.UpdateCommand = cmd;
                        break;
                    case CommandOperation.Delete:
                        adapter.DeleteCommand = cmd;
                        break;
                    default:
                        break;
                }
                int result = adapter.Update(ds, tableName);

                //int result = cmd.ExecuteNonQuery();

                Transaction.Commit();
                return result > 0 ? true : false;
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                Transaction.Dispose();

                throw ex;
            }
            finally { this.Close(); }
            //return (int)cmd.Parameters["ReturnValue"].Value;
        }

        #endregion

        #region   Excute query command
        /// <summary>
        ///  Excute query command and return dataset
        /// </summary>
        /// <param name="procName">procName</param>
        /// <param name="prams">prams</param>
        /// <param name="tbName">tbName</param>
        /// <returns></returns>
        public DataSet ExcuteQuery(string procName, SqlParameter[] prams, string tbName)
        {
            SqlDataAdapter dap = CreateDataAdapter(procName, prams);
            DataSet ds = new DataSet();
            dap.Fill(ds, tbName);
            this.Close();

            return ds;
        }

        public void ExcuteQuery(string procName, DataTable dt, SqlParameter[] prams)
        {
            SqlDataAdapter dap = CreateDataAdapter(procName, prams);
            dap.Fill(dt);
            this.Close();
        }

        /// <summary>
        /// Excute query command and return dataset
        /// </summary>
        /// <param name="procName">procName</param>
        /// <param name="tbName">tbName</param>
        /// <returns>DataSet</returns>
        public DataSet ExcuteQuery(string procName, string tbName)
        {
            SqlDataAdapter dap = CreateDataAdapter(procName, null);
            DataSet ds = new DataSet();
            dap.Fill(ds, tbName);
            this.Close();

            return ds;
        }

        #endregion

        #region Create data adapter
        /// <summary>
        /// Create data adapter
        /// </summary>
        /// <param name="procName">procName</param>
        /// <param name="prams">prams</param>
        /// <returns></returns>
        private SqlDataAdapter CreateDataAdapter(string procName, SqlParameter[] prams)
        {
            this.Open();
            SqlDataAdapter dap = new SqlDataAdapter(procName, con);
            dap.SelectCommand.CommandType = CommandType.StoredProcedure;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                    dap.SelectCommand.Parameters.Add(parameter);
            }

            /*
            dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));
            */
            return dap;
        }
        #endregion

        #region   Crate command
        /// <summary>
        ///  Crate command
        /// </summary>
        /// <param name="procName">procName</param>
        /// <param name="prams">prams</param>
        /// <returns></returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {

            this.Open();
            SqlCommand cmd = new SqlCommand(procName, con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Transaction = Transaction;

            if (prams != null && prams.Length > 0)
            {
                foreach (SqlParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);
                }
            }

            /*
            cmd.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));
            */
            return cmd;
        }
        #endregion

        #region Transaction
        private SqlTransaction _sqlTrans = null;

        private SqlTransaction CreateTransction()
        {
            if (_sqlTrans == null)
            {
                _sqlTrans = con.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            return _sqlTrans;
        }

        private SqlTransaction Transaction
        {
            get
            {
                return this.CreateTransction();
            }
            set { }
        }
        #endregion
    }
}
